package com.sqlparser.util;

import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLName;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.SQLExprTableSource;
import com.alibaba.druid.sql.ast.statement.SQLSelect;
import com.alibaba.druid.sql.ast.statement.SQLWithSubqueryClause;
import com.alibaba.druid.sql.dialect.hive.ast.HiveInsertStatement;
import com.alibaba.druid.sql.dialect.hive.parser.HiveStatementParser;
import com.alibaba.druid.sql.parser.ParserException;
import com.alibaba.druid.sql.parser.SQLParserUtils;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.sqlparser.dto.DataLineageDto;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.create.view.CreateView;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.drop.Drop;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.Update;

import java.io.*;
import java.util.*;

public class SqlParserUtil {


    public static List<DataLineageDto> parseViewDefinitionDruid(String viewSql) {

            return createParser(viewSql);
    }
    /**
     * 解析视图的SQL定义
     *
     * @param viewSql 视图的SQL定义字符串
     * @return 视图名称和SQL语句的字符串表示
     */

    public static List<DataLineageDto> parseViewDefinition(String viewSql) {
        try {
            // 解析SQL语句
            Statement statement = CCJSqlParserUtil.parse(viewSql);

            // 检查是否为创建视图的语句
            if (statement instanceof CreateView) {
                CreateView createView = (CreateView) statement;
                // 获取视图名称
                String viewName = createView.getView().getFullyQualifiedName();
                // 获取视图的SQL定义（可选，直接返回原始SQL也是一种方式）
                String sqlDefinition = createView.getSelect().toString();
                // 这里只是简单地返回视图名称和SQL定义，实际应用中你可能需要更复杂的处理
                return null;
            }
            //解析select语句
            if (statement instanceof Select) {
                Select select = (Select) statement;
                List<DataLineageDto> dataLineageDtos = analysisSelect(select);
                List<WithItem> withItemsList = select.getWithItemsList();
                if (withItemsList != null && withItemsList.size() > 0) {
                    getWithItem(withItemsList, dataLineageDtos);
                }
                return dataLineageDtos;
            }
            //解析insert语句
            if (statement instanceof Insert) {
                Insert insert = (Insert) statement;
                List<Column> columns = insert.getColumns();
                return null;
            }
            //解析update语句
            if (statement instanceof Update) {
                Update update = (Update) statement;
                Table table = update.getTable();
                return null;
            }
            //解析delete语句
            if (statement instanceof Delete) {
                Delete delete = (Delete) statement;
                Table table = delete.getTable();
                return null;
            }
            if (statement instanceof Drop) {
                Drop drop = (Drop) statement;
                List<DataLineageDto> list = new ArrayList<>();
                String type = drop.getType().toLowerCase();
                if (type.equals("table")) {
                    Table table = drop.getName();
                    if (table != null) {
                        DataLineageDto dataLineageDto = new DataLineageDto();
                        dataLineageDto.setSourceTable(table.getName());
                        dataLineageDto.setSourceAliasName(table.getAlias() != null ? table.getAlias().getName() : null);
                        dataLineageDto.setType("DROP");
                        list.add(dataLineageDto);
                    }

                }
                return list;
            }
            if (statement instanceof CreateTable) {
                CreateTable createTable = (CreateTable) statement;
                List<ColumnDefinition> columnDefinitions = createTable.getColumnDefinitions();
                List<DataLineageDto> list = new ArrayList<>();
                Table table = createTable.getTable();
                if (table != null) {
                    List<Map<String, String>> cloumnNames = new ArrayList<>();
                    for (ColumnDefinition columnDefinition : columnDefinitions) {
                        Map<String, String> map = new HashMap<>(2);
                        map.put("columnName", columnDefinition.getColumnName());
                        map.put("aliasName", null);
                        cloumnNames.add(map);
                    }
                    DataLineageDto dataLineageDto = new DataLineageDto();
                    dataLineageDto.setSourceTable(table.getName());
                    dataLineageDto.setSourceAliasName(table.getAlias() != null ? table.getAlias().getName() : null);
                    dataLineageDto.setColumnItems(cloumnNames);
                    dataLineageDto.setType("CREATE");
                    list.add(dataLineageDto);

                }
                return list;
            } else {
                System.out.println("无法解析sql语句，请检查语法是否正确");
                return null;
            }
        } catch (JSQLParserException e) {
            e.printStackTrace();
            System.out.println("Error parsing SQL: " + e.getMessage());
            return null;
        }
    }

    public static List<DataLineageDto> createParser(String sql) {
        List<DataLineageDto> list = new ArrayList<>();
        try {
            // 使用通用的 SQL 解析器
            SQLStatementParser parser = new HiveStatementParser(sql);
            SQLStatement stmt = parser.parseStatement();
            if (stmt instanceof HiveInsertStatement) {
                list = createParser((HiveInsertStatement) stmt);
            }else{
                list =  parseViewDefinition(sql);
            }
            System.out.println("SQL语句解析成功：" + stmt.toString());
        } catch (ParserException e) {
            System.err.println("解析Hive SQL语句时出错：" + e.getMessage());
            e.printStackTrace();
        }

        return list;
    }

    public static List<DataLineageDto> createParser(HiveInsertStatement stmt) {
        List<DataLineageDto> list = new ArrayList<>();
        SQLExprTableSource tableSource = stmt.getTableSource();
        SQLExpr expr = tableSource.getExpr();
        SQLSelect query = stmt.getQuery();
        String tableName = null;
        if (expr instanceof SQLName) {
            tableName = ((SQLName) expr).getSimpleName();
        }
        SQLWithSubqueryClause with = stmt.getWith();
        if (with != null) {
            // List<WithItem> withItemsList = with.get();
            // SqlParserUtil.getWithItem(withItemsList, list);
            List<SQLWithSubqueryClause.Entry> entries = with.getEntries();
            for (SQLWithSubqueryClause.Entry entry : entries) {
                String name = entry.getAlias().toString();
                String sql = entry.getSubQuery().toString();
                String alias = entry.getAlias();
                list.addAll(SqlParserUtil.parseViewDefinition(sql));
            }
        }
        DataLineageDto dataLineageDto = new DataLineageDto();
        dataLineageDto.setSourceTable(tableName);
        dataLineageDto.setType("INSERT");
        list.add(dataLineageDto);
        if (query != null) {
            SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(query.toString(), "hive");
            List<SQLStatement> stmts = parser.parseStatementList();
            for (SQLStatement stmt1 : stmts) {
                list.addAll(SqlParserUtil.parseViewDefinition(stmt1.toString()));
            }
        }
        return list;
    }


    /**
     * 遍历WITH子句中的每个WITH项，并对其进行分析。
     *
     * @param withItemsList   包含所有WITH项的列表。
     * @param dataLineageDtos 用于存储分析结果的数据血缘列表。
     */
    public static void getWithItem(List<WithItem> withItemsList, List<DataLineageDto> dataLineageDtos) {
        // 遍历WITH子句中的每个WITH项
        for (WithItem withItem : withItemsList) {
            // 将WITH项中的子查询转换为SubSelect对象，并对其进行分析
            SubSelect subSelect = (SubSelect) withItem.getSubSelect();
            analysisSelect(dataLineageDtos, subSelect);
        }
    }


    /**
     * 分析SELECT语句以提取数据血缘信息。
     * 该方法主要处理包含简单SELECT语句或复合SELECT语句（如UNION）的输入。
     * 它通过递归处理各种SELECT语句的结构，收集与数据源和数据操作相关的元数据信息。
     *
     * @param select 输入的SELECT语句对象，包含完整的SQL选择语句结构。
     * @return 返回一个包含数据血缘信息的DTO（数据传输对象）列表。
     */
//解析select语句主
    public static List<DataLineageDto> analysisSelect(Select select) {
        SelectBody selectBody = select.getSelectBody();
        List<DataLineageDto> list = new ArrayList<>();
        // 如果SELECT语句主体是一个SetOperationList（例如UNION操作），则递归处理每个子SELECT语句
        if (selectBody instanceof SetOperationList) {
            SetOperationList selectBodyc = (SetOperationList) selectBody;
            List<SelectBody> selects = selectBodyc.getSelects();
            for (SelectBody sb : selects) {
                extracted(list, (PlainSelect) sb);
            }
        }
        // 如果SELECT语句主体是一个简单的SELECT语句，则直接处理该语句
        if (selectBody instanceof PlainSelect) {
            extracted(list, (PlainSelect) selectBody);
        }
        return list;

    }


    /**
     * 分析子查询以提取数据血缘信息。
     * 该方法处理两种类型的查询：SetOperationList和PlainSelect。它首先检查是否是SetOperationList类型，
     * 这种类型代表了SQL中联合查询的结构。如果是，它会递归处理每个子查询。
     * 如果不是SetOperationList类型，则假设它是一个PlainSelect查询，直接处理该查询。
     * 这种方法的核心目的是为了从复杂的SQL查询中提取出涉及的表和字段信息，以建立数据血缘关系。
     *
     * @param select 子查询对象，包含了待分析的查询结构。
     * @return 返回一个列表，包含了分析过程中提取的数据血缘信息。
     */
    public static List<DataLineageDto> analysisSelect(List<DataLineageDto> list, SubSelect select) {
        // 获取子查询的主体部分
        SelectBody selectBody = select.getSelectBody();
        // 初始化一个列表，用于存储分析过程中提取的数据血缘信息


        // 检查是否是SetOperationList类型，即是否包含多个查询的联合操作
        if (selectBody instanceof SetOperationList) {
            // 类型转换，以便能访问SetOperationList特有的方法和属性
            SetOperationList selectBodyc = (SetOperationList) selectBody;
            // 获取所有的子查询
            List<SelectBody> selects = selectBodyc.getSelects();
            // 遍历每个子查询，并递归调用analysisSelect方法进行处理
            for (SelectBody sb : selects) {
                extracted(list, (PlainSelect) sb);
            }
        }

        // 检查是否是PlainSelect类型，即普通的查询语句
        if (selectBody instanceof PlainSelect) {
            // 类型转换，以便能访问PlainSelect特有的方法和属性
            extracted(list, (PlainSelect) selectBody);
        }

        // 返回包含数据血缘信息的列表
        return list;
    }

    /**
     * 提取SQL语句中的数据血缘信息。
     * 该方法解析一个SELECT语句，从中提取出表之间的关联信息以及列的来源和目标，
     * 以DataLineageDto对象的形式存储，用于后续的数据血缘分析。
     *
     * @param list 存储提取的数据血缘信息的列表。
     * @param ps   SQL语句的PlainSelect对象，代表一个SELECT语句的解析树。
     */
    private static void extracted(List<DataLineageDto> list, PlainSelect ps) {
        PlainSelect plainSelect = ps;

        // 获取SELECT语句中的列信息
        //获取select语句中的列
        List<SelectItem> selectItems = plainSelect.getSelectItems();
        // 获取FROM子句的信息
        //获取select语句中的表
        FromItem fromItem = plainSelect.getFromItem();
        // 初始化表名和别名
        String tableName = null;
        String aliasName = null;

        // 如果FROM项是一个表，则提取表名和别名
        if (fromItem instanceof Table) {
            //获取表名
            Table table = (Table) fromItem;
            //别名
            Alias alias = table.getAlias();
            if (alias != null) {
                aliasName = alias.getName();
            }
            tableName = table.getName();
        }

        // 如果FROM项是一个子查询，则递归分析子查询
        if (fromItem instanceof SubSelect) {
            //获取子查询
            SubSelect subSelect = (SubSelect) fromItem;
            analysisSelect(list, subSelect);
        }

        // 获取JOIN信息
        List<Join> joins = plainSelect.getJoins();
        Expression where = plainSelect.getWhere();
        // 遍历JOIN列表，提取关联信息
        if (joins != null) {
            for (Join join : joins) {
                //获取join语句中的表
                FromItem rightItem = join.getRightItem();
                // 初始化目标表名和别名
                String targetName = null;
                String targerAliasName = null;

                // 如果右表是一个表，则提取表名和别名
                if (rightItem instanceof Table) {
                    //获取表名
                    Table table = (Table) rightItem;
                    targetName = table.getName();
                    targerAliasName = table.getAlias().getName();
                }
                // 如果右表是一个子查询，则递归分析子查询
                if (rightItem instanceof SubSelect) {
                    //获取子查询
                    SubSelect subSelect = (SubSelect) rightItem;
                    analysisSelect(list, subSelect);
                }

                // 处理ON条件表达式，提取关联列信息
                Collection<Expression> onExpressions = join.getOnExpressions();
                if (onExpressions.size() > 0) {
                    for (Expression onExpression : onExpressions) {
                        if (onExpression instanceof AndExpression) {
                            getWhereInfo(list, (AndExpression) onExpression, tableName, aliasName);
                        }
                        if (onExpression instanceof EqualsTo) {
                            EqualsTo equalsTo = (EqualsTo) onExpression;
                            Column column = (Column) equalsTo.getLeftExpression();
                            //获取列名 别名 MDR_M
                            Table table = column.getTable();
                            String leftName = table.getName();
                            //获取列名-条件列名
                            String leftColumnName = column.getColumnName();

                            Column rightcolumn = (Column) equalsTo.getRightExpression();
                            //获取列名 MDR_M
                            // Table rightcolumnTable = rightcolumn.getTable();
                            String rightName = rightcolumn.getTable().getName();
                            //获取列名-条件列名
                            String rightColumnName = rightcolumn.getColumnName();

                            // 根据左右表的别名，构造数据血缘信息
                            DataLineageDto dataLineageDto = new DataLineageDto();
                            if (leftName.equals(aliasName)) {
                                dataLineageDto.setSourceTable(tableName);
                                dataLineageDto.setTargetTable(targetName);
                                dataLineageDto.setTargetColumn(rightColumnName);
                                dataLineageDto.setSourceColumn(leftColumnName);
                                dataLineageDto.setSourceAliasName(aliasName);
                                dataLineageDto.setTargetAliasName(rightName);

                            } else {
                                dataLineageDto.setSourceTable(tableName);
                                dataLineageDto.setTargetTable(targetName);
                                dataLineageDto.setTargetColumn(leftColumnName);
                                dataLineageDto.setSourceColumn(rightColumnName);
                                dataLineageDto.setSourceAliasName(aliasName);
                                dataLineageDto.setTargetAliasName(leftName);

                            }
                            dataLineageDto.setType("SELECT");
                            list.add(dataLineageDto);
                        }
                    }
                } else {
                    // 如果没有ON条件，仍然构造一个数据血缘信息对象
                    DataLineageDto dataLineageDto = new DataLineageDto();
                    dataLineageDto.setSourceTable(tableName);
                    dataLineageDto.setTargetTable(targetName);
                    dataLineageDto.setSourceAliasName(aliasName);
                    dataLineageDto.setTargetAliasName(targerAliasName);
                    dataLineageDto.setType("SELECT");
                    list.add(dataLineageDto);
                }

            }
        }

        // 处理WHERE条件，进一步提取数据血缘信息
        if (where != null) {
            if (where instanceof AndExpression) {
                AndExpression andExpression = (AndExpression) where;
                getWhereInfo(list, andExpression, tableName, aliasName);
            }
            if (where instanceof EqualsTo) {
                EqualsTo equalsTo = (EqualsTo) where;
                getWhereInfoEq(list, equalsTo, tableName, aliasName);
            }
        }
        if (joins == null && where == null) {
            DataLineageDto dataLineage = new DataLineageDto();
            dataLineage.setSourceTable(tableName);
            dataLineage.setSourceAliasName(aliasName);
            dataLineage.setType("SELECT");
            list.add(dataLineage);
        }
        // 处理SELECT列表中的列信息，提取列名和别名
        List<Map<String, String>> columnList = new ArrayList<>();
        if (selectItems != null) {
            for (SelectItem selectItem : selectItems) {
                if (selectItem instanceof SelectExpressionItem) {
                    SelectExpressionItem selectExpressionItem = (SelectExpressionItem) selectItem;
                    Expression expression = selectExpressionItem.getExpression();
                    if (expression instanceof Column) {
                        Column column = (Column) expression;
                        String columnName = column.getColumnName();
                        String tableName1 = column.getTable() == null ? null : column.getTable().getName();
                        Map<String, String> map = new HashMap<>(2);
                        map.put("columnName", columnName);
                        map.put("aliasName", tableName1);
                        columnList.add(map);
                    }
                    if (list.size() > 0) {
                        list.get(0).setColumnItems(columnList);
                    }
                }
            }
        }
    }


    /**
     * 根据等于条件获取数据血缘信息。
     * 该方法处理等于（EqualsTo）表达式，以确定源表、目标表、别名和列名之间的关系。
     * 它主要处理两种情况：列与列的比较和列与常量的比较。
     *
     * @param relust          存储数据血缘信息的列表，会在这个方法中添加新的数据血缘信息。
     * @param equalsTo        等于条件表达式，包含左右两个表达式。
     * @param sourceTableName 源表的名称。
     * @param aliasName       别名，用于匹配表或列。
     */
    public static void getWhereInfoEq(List<DataLineageDto> relust, EqualsTo equalsTo, String sourceTableName, String aliasName) {
        // 获取等于条件的左表达式
        Expression leftExpression1 = equalsTo.getLeftExpression();
        // 获取等于条件的右表达式
        Expression rcolumn = equalsTo.getRightExpression();
        // 初始化数据血缘信息对象
        DataLineageDto dataLineageDto = new DataLineageDto();

        // 处理左表达式是列的情况
        if (leftExpression1 instanceof Column) {
            Column leftExpression11 = (Column) leftExpression1;
            // 如果列的表不为空
            if (leftExpression11.getTable() != null) {
                // 获取列所在的表名
                String tableName = leftExpression11.getTable().getName();
                // 获取列名
                String columnName = leftExpression11.getColumnName();
                // 如果表名与别名匹配，则认为是源表，否则为目标表
                if (aliasName.equals(tableName)) {
                    dataLineageDto.setSourceTable(sourceTableName);
                    dataLineageDto.setSourceAliasName(aliasName);
                    dataLineageDto.setSourceColumn(columnName);
                } else {
                    dataLineageDto.setTargetTable(tableName);
                    dataLineageDto.setTargetColumn(columnName);
                    dataLineageDto.setTargetAliasName(tableName);
                }
            }
        }
        // 处理左表达式是常量字符串的情况
        if (leftExpression1 instanceof StringValue) {
            StringValue leftExpression11 = (StringValue) leftExpression1;
            // 如果常量字符串不为空
            if (leftExpression11 != null) {
                // 设置目标列名为常量字符串的值
                String leftValue = leftExpression11.getValue();
                dataLineageDto.setTargetColumn(leftValue);
            }
        }

        // 处理右表达式是列的情况
        if (rcolumn instanceof Column) {
            Column rightExpression11 = (Column) rcolumn;
            // 如果列的表不为空
            if (rightExpression11.getTable() != null) {
                // 获取列所在的表名
                String tableName = rightExpression11.getTable().getName();
                // 获取列名
                String columnName = rightExpression11.getColumnName();
                // 如果表名与别名匹配，则认为是源表，否则为目标表
                if (aliasName.equals(tableName)) {
                    dataLineageDto.setSourceTable(sourceTableName);
                    dataLineageDto.setSourceAliasName(aliasName);
                    dataLineageDto.setSourceColumn(columnName);
                } else {
                    dataLineageDto.setTargetTable(tableName);
                    dataLineageDto.setTargetColumn(columnName);
                    dataLineageDto.setTargetAliasName(tableName);
                }
            }
        }
        // 处理右表达式是常量字符串的情况
        if (rcolumn instanceof StringValue) {
            StringValue rightExpression11 = (StringValue) rcolumn;
            // 如果常量字符串不为空
            if (rightExpression11 != null) {
                // 设置目标列名为常量字符串的值
                String right = rightExpression11.getValue();
                dataLineageDto.setTargetColumn(right);
            }
        }
        dataLineageDto.setType("SELECT");
        // 将处理好的数据血缘信息添加到列表中
        relust.add(dataLineageDto);
    }

    /**
     * 根据给定的AND表达式，从数据血缘关系列表中提取相关信息。
     * 此方法用于处理复杂的查询条件，通过分解AND表达式来提取左右两个子表达式的信息。
     *
     * @param relust          数据血缘关系列表，存储查询结果。
     * @param andExpression   AND表达式，包含左右两个子表达式。
     * @param sourceTableName 源表名称，用于匹配数据血缘关系。
     * @param aliasName       别名，用于匹配数据血缘关系。
     */
    public static void getWhereInfo(List<DataLineageDto> relust, AndExpression andExpression, String sourceTableName, String aliasName) {
        // 如果AND表达式不为空，则进一步处理左右两个子表达式
        if (andExpression != null) {
            // 提取AND表达式的左子表达式，并将其相关信息添加到数据血缘关系列表中
            Expression leftExpression = andExpression.getLeftExpression();
            extracted(relust, sourceTableName, aliasName, leftExpression);

            // 提取AND表达式的右子表达式，并将其相关信息添加到数据血缘关系列表中
            Expression rightExpression = andExpression.getRightExpression();
            extracted(relust, sourceTableName, aliasName, rightExpression);
        }
    }

    /**
     * 根据给定的AND表达式，从数据血缘关系列表中提取相关信息。
     * 此方法用于处理WHERE子句中的AND条件，将左右两个表达式的影响范围提取出来。
     *
     * @param relust          数据血缘关系列表，存储提取出的信息。
     * @param andExpression   AND表达式，用于进一步提取血缘关系信息。
     * @param sourceTableName 源表名称，用于标识数据来源。
     * @param aliasName       别名，用于处理表的别名情况。
     */
    public static void getWhereInfo(List<DataLineageDto> relust, InExpression andExpression, String sourceTableName, String aliasName) {
        // 如果AND表达式不为空，则进一步处理左右两个表达式
        if (andExpression != null) {
            // 提取AND表达式的左表达式，并将其影响范围添加到数据血缘关系列表中
            Expression leftExpression = andExpression.getLeftExpression();
            extracted(relust, sourceTableName, aliasName, leftExpression);
            // 提取AND表达式的右表达式，并将其影响范围添加到数据血缘关系列表中
            Expression rightExpression = andExpression.getRightExpression();
            extracted(relust, sourceTableName, aliasName, rightExpression);
            ItemsList rightItemsList = andExpression.getRightItemsList();
            if (rightItemsList != null) {
                if (rightItemsList instanceof SubSelect) {
                    SubSelect subSelect = (SubSelect) rightItemsList;
                    analysisSelect(relust, subSelect);
                }
            }
        }
    }

    /**
     * 解析WHERE子句中的表达式，以提取数据血缘信息。
     *
     * @param relust          用于存储提取的数据血缘信息的列表。
     * @param sourceTableName 来源表的名称。
     * @param aliasName       别名，用于匹配表或列的别名。
     * @param expression      WHERE子句中的表达式，用于解析血缘信息。
     */
    private static void extracted(List<DataLineageDto> relust, String sourceTableName, String aliasName, Expression expression) {
        // 处理逻辑与表达式
        if (expression instanceof AndExpression) {
            getWhereInfo(relust, (AndExpression) expression, sourceTableName, aliasName);
        }
        // 处理IN表达式
        if (expression instanceof InExpression) {
            InExpression inExpression = (InExpression) expression;
            getWhereInfo(relust, (InExpression) inExpression, sourceTableName, aliasName);
        }

        // 处理等于表达式
        if (expression instanceof EqualsTo) {
            EqualsTo equalsTo = (EqualsTo) expression;
            Expression leftExpression1 = equalsTo.getLeftExpression();
            Expression rcolumn = equalsTo.getRightExpression();
            DataLineageDto dataLineageDto = new DataLineageDto();
            // 处理左侧列为列名的情况
            if (leftExpression1 instanceof Column) {
                Column leftExpression11 = (Column) leftExpression1;
                if (leftExpression11.getTable() != null) {
                    String tableName = leftExpression11.getTable().getName();
                    String columnName = leftExpression11.getColumnName();
                    if (aliasName.equals(tableName)) {
                        dataLineageDto.setSourceTable(sourceTableName);
                        dataLineageDto.setSourceAliasName(aliasName);
                        dataLineageDto.setSourceColumn(columnName);
                    } else {
                        dataLineageDto.setTargetTable(tableName);
                        dataLineageDto.setTargetColumn(columnName);
                        dataLineageDto.setTargetAliasName(tableName);
                    }
                }
                if(leftExpression11.getColumnName()!=null){
                    dataLineageDto.setSourceColumn(leftExpression11.getColumnName());
                }
            }
            // 处理左侧列为字符串值的情况
            if (leftExpression1 instanceof StringValue) {
                StringValue leftExpression11 = (StringValue) leftExpression1;
                if (leftExpression11 != null) {
                    String leftValue = leftExpression11.getValue();
                    System.out.println(leftValue);
                    dataLineageDto.setTargetColumn(leftValue);
                }
            }
            // 处理右侧列为列名的情况
            if (rcolumn instanceof Column) {
                Column rightExpression11 = (Column) rcolumn;
                if (rightExpression11.getTable() != null) {
                    String tableName = rightExpression11.getTable().getName();
                    String columnName = rightExpression11.getColumnName();
                    if (aliasName.equals(tableName)) {
                        dataLineageDto.setSourceTable(sourceTableName);
                        dataLineageDto.setSourceAliasName(aliasName);
                        dataLineageDto.setSourceColumn(columnName);
                    } else {
                        dataLineageDto.setTargetTable(tableName);
                        dataLineageDto.setTargetColumn(columnName);
                        dataLineageDto.setTargetAliasName(tableName);
                    }
                }
            }
            // 处理右侧列为字符串值的情况
            if (rcolumn instanceof StringValue) {
                StringValue rightExpression11 = (StringValue) rcolumn;
                if (rightExpression11 != null) {
                    String right = rightExpression11.getValue();
                    dataLineageDto.setTargetColumn(right);
                }
            }
            // 处理右侧列为长整型值的情况
            if (rcolumn instanceof LongValue) {
                LongValue rightExpression11 = (LongValue) rcolumn;
                if (rightExpression11 != null) {
                    Long right = rightExpression11.getValue();
                    dataLineageDto.setTargetColumn(right + "");
                }
            }
            // 处理右侧列为表达式列表的情况
            if (rcolumn instanceof ExpressionList) {
                ExpressionList rightExpression11 = (ExpressionList) rcolumn;
                if (rightExpression11 != null) {
                    List<Expression> right = rightExpression11.getExpressions();
                    dataLineageDto.setTargetColumn(right.toString());
                }
            }
            dataLineageDto.setType("SELECT");
            relust.add(dataLineageDto);
        }
    }


    public static String formatSql(String sql){
        return sql.replaceAll("\\s+", " ").trim();
    }

    public static void main(String[] args) {
        String filePath = "G:\\xyfx\\hivesql.txt";

        try (BufferedReader reader = new BufferedReader(new FileReader(new File(filePath)))) {
            String currentLine=null;
            StringBuilder sb=new StringBuilder();
            // 循环读取每一行直到文件结束
            while ((currentLine = reader.readLine()) != null) {
                // 输出当前行的内容
                sb.append(currentLine);
            }
            List<String> viewSql = Arrays.asList(sb.toString().split(";"));
            /*for(String str:viewSql){
                 List<DataLineageDto> result = parseViewDefinitionDruid(str);
                System.out.println(result);
            }*/
String sql="WITH MATERIAL_PLAN_relate as (\n" +
        "  select\n" +
        "    DI.date_source AS date_source,\n" +
        "    DI.MERP_EWO_ID AS MERP_EWO_ID,\n" +
        "    --mes_ewo_id\n" +
        "    case\n" +
        "      WHEN INSTR(DI.MS_WORKLINE, CSRY.N_DEPT) = 1\n" +
        "      OR INSTR(DI.MS_WORKLINE, CSRY.o_DEPT) = 1 THEN '1'\n" +
        "      else '0'\n" +
        "    end AS first_DEPT_materail,\n" +
        "    --是否首车间器材\n" +
        "    --有效器材代码\n" +
        "    case\n" +
        "      WHEN CAST(CSRY.EFFECTITVE_LOT AS INT) <= CAST(DI.LOT AS INT)\n" +
        "      AND CAST(CSRY.INEFFECTITVE_LOT AS INT) >= CAST(DI.LOT AS INT) THEN 1\n" +
        "      ELSE 0\n" +
        "    END AS CSRY_MATERIAL_VALID,\n" +
        "    CSRY.N_DEPT AS CSRY_N_DEPT,\n" +
        "    CSRY.o_DEPT AS CSRY_o_DEPT,\n" +
        "    CSRY.SINGLE_RATION_CLASS AS CSRY_SINGLE_RATION_CLASS,\n" +
        "    --材料定额：定额类型：0--原材料。1--锻铸件\n" +
        "    CSRY.SINGLE_RATION_ID AS CSRY_SINGLE_RATION_ID,\n" +
        "    --材料定额：定额ID\n" +
        "    CSRY.MATERIAL_CODE AS CSRY_MATERIAL_CODE,\n" +
        "    --材料定额：定额代码\n" +
        "    CSRY.SINGLE_RATION AS CSRY_SINGLE_RATION,\n" +
        "    --材料定额：审批单件综合定额\n" +
        "    CSRY.DRAFT_SIZE AS CSRY_DRAFT_SIZE,\n" +
        "    --材料定额：图样尺寸\n" +
        "    CSRY.FEEDING_SIZE AS CSRY_FEEDING_SIZE,\n" +
        "    --材料定额：下料尺寸\n" +
        "    CSRY.BLANK_SIZE AS CSRY_BLANK_SIZE,\n" +
        "    CSMI.Drawing_Code AS CSMI_Drawing_Code,\n" +
        "    --采购系统：物料图号\n" +
        "    CSMI.Item_Name AS CSMI_Item_Name,\n" +
        "    --采购系统：物料名称\n" +
        "    CSMI.Marque AS CSMI_Marque,\n" +
        "    --采购系统：牌号\n" +
        "    CSMI.Specs AS CSMI_Specs,\n" +
        "    --采购系统：规格\n" +
        "    CSMI.Tech_Condition AS CSMI_Tech_Condition,\n" +
        "    --采购系统：技术条件\n" +
        "    CSMI.Gt_Code AS CSMI_Gt_Code,\n" +
        "    --采购系统：附件技术条件\n" +
        "    CSMI.Mds_Item_Class_Code AS CSMI_Mds_Item_Class_Code,\n" +
        "    --采购系统：物料类型代码\n" +
        "    CSMI.Mds_Item_Class_Name AS CSMI_Mds_Item_Class_Name,\n" +
        "    --采购系统：物料类型名称\n" +
        "    CSMI.Mds_Item_Class_Desc AS CSMI_Mds_Item_Class_Desc,\n" +
        "    --采购系统：物料类型路径\n" +
        "    CSMI.Item_Type AS CSMI_Item_Type,\n" +
        "    CSMI.RATION_UNIT_Desc AS CSMI_RATION_UNIT_Desc,\n" +
        "    --采购系统：定额计量单位\n" +
        "    CSMI.RATION_TO_WMS AS CSMI_RATION_TO_WMS,\n" +
        "    --采购系统：定额折库存系数\n" +
        "    CSRY.SINGLE_RATION / CSMI.RATION_TO_WMS AS CSMI_WMS_RATION,\n" +
        "    --器材用量（库存单位）\n" +
        "    CSMI.WMS_Unit_Desc AS CSMI_WMS_Unit_Desc,\n" +
        "    --采购系统：库存计量单位\n" +
       "    CSMI.GN_planned_price AS GN_planned_price,\n" +
        "    --采购系统：GN项目计划价\n" +
        "    CSMI.JJ_planned_price AS JJ_planned_price,\n" +
        "    --采购系统：JJ项目计划价\n" +
        "    CSMI.JMY21_planned_price AS JMY21_planned_price,\n" +
        "    --采购系统：JMY21项目计划价\n" +
        "    CSMI.JM_planned_price AS JM_planned_price,\n" +
        "    --采购系统：JM项目计划价\n" +
        "    CSMI.ZB_planned_price AS ZB_planned_price,\n" +
        "    --采购系统：ZB项目计划价\n" +
        "    -----------------\n" +
        "    SSPM.Project_Code AS Project_Code,\n" +
        "    --器材需求的项目\n" +
        "    CASE\n" +
        "      WHEN SSPM.Project_Code = 'GN'\n" +
        "      AND CSMI.GN_planned_price > 0\n" +
        "      AND CSMI.GN_planned_price IS NOT NULL THEN CSMI.GN_planned_price\n" +
        "      WHEN SSPM.Project_Code = 'JJ'\n" +
        "      AND CSMI.JJ_planned_price > 0\n" +
        "      AND CSMI.JJ_planned_price IS NOT NULL THEN CSMI.JJ_planned_price\n" +
        "      WHEN SSPM.Project_Code = 'JMY21'\n" +
        "      AND CSMI.JMY21_planned_price > 0\n" +
        "      AND CSMI.JMY21_planned_price IS NOT NULL THEN CSMI.JMY21_planned_price\n" +
        "      WHEN SSPM.Project_Code = 'JM'\n" +
        "      AND CSMI.JM_planned_price > 0\n" +
        "      AND CSMI.JM_planned_price IS NOT NULL THEN CSMI.JM_planned_price\n" +
        "      WHEN SSPM.Project_Code = 'ZB'\n" +
        "      AND CSMI.ZB_planned_price > 0\n" +
        "      AND CSMI.ZB_planned_price IS NOT NULL THEN CSMI.ZB_planned_price\n" +
        "      ELSE NULL\n" +
        "    END AS MATERIAL_Price,\n" +
        "    --器材项目计划价\n" +
        "    0 AS JE,\n" +
        "    --器材金额\n" +
        "    DI.LOT AS DI_LOT,\n" +
        "    --计划最大架次\n" +
        "    DI.MS_WORKLINE AS MS_WORKLINE,\n" +
        "    CSRY.EFFECTITVE_LOT AS CSRY_EFFECTITVE_LOT,\n" +
        "    --材料定额：起架次\n" +
        "    CSRY.INEFFECTITVE_LOT AS CSRY_INEFFECTITVE_LOT,\n" +
        "    --材料定额：止架次\n" +
        "    CSRY.DEPT_CODE AS CSRY_DEPT_CODE --材料定额：起架次\n" +
        "  FROM\n" +
        "    xactest.COST_EWO_DEPT_INFO DI --关联材料定额(按图号关联：扩展)\n" +
        "    LEFT JOIN xactest.COST_SINGLE_RATION_YCLDZJ CSRY ON CSRY.PLANE_TYPE = 'ZCP'\n" +
        "    AND CSRY.ITEM_CODE = DI.ITEM_CODE --关联器材信息（按物料关联：不扩展）\n" +
        "    LEFT JOIN xactest.COST_SRM_MDS_ITEM CSMI ON CSMI.Item_Code = CSRY.MATERIAL_CODE\n" +
        "    LEFT JOIN xac_erp.ODS_SRM_MDS_ITEM_PROJECT SMIP ON SMIP.ITEM_CODE = CSRY.MATERIAL_CODE\n" +
        "    LEFT JOIN xac_erp.ODS_SRM_Srm_Project_Moprtdef SSPM ON SSPM.PRODUCT_CODE = DI.PLAN_CODE\n" +
        "    AND SSPM.Project_Code = SMIP.Project_Code\n" +
        ")\n" +
        "INSERT\n" +
        "  overwrite table xactest.COST_MATERIAL_PLAN_INFO\n" +
        "SELECT\n" +
        "  date_source,\n" +
        "  MERP_EWO_ID,\n" +
        "  --mes_ewo_id\n" +
        "  ------------------------------------------------------------------------------------------------------------\n" +
        "  MAX(CSRY_SINGLE_RATION_ID),\n" +
        "  --定额主键\n" +
        "  MAX(first_DEPT_materail),\n" +
        "  --是否首车间器材\n" +
        "  MAX(CSRY_SINGLE_RATION_CLASS),\n" +
        "  --材料定额：定额类型：0--原材料。1--锻铸件\n" +
        "  CSRY_MATERIAL_CODE,\n" +
        "  --材料定额：定额代码\n" +
        "  MAX(CSRY_SINGLE_RATION),\n" +
        "  --材料定额：审批单件综合定额\n" +
        "  MAX(CSRY_DRAFT_SIZE),\n" +
        "  --材料定额：图样尺寸\n" +
        "  MAX(CSRY_FEEDING_SIZE),\n" +
        "  --材料定额：下料尺寸\n" +
        "  MAX(CSRY_BLANK_SIZE),\n" +
        "  --材料定额：毛料尺寸\n" +
        "  ------------------------------------------------------------------------------------------------------------\n" +
        "  MAX(CSMI_Drawing_Code),\n" +
        "  --采购系统：物料图号\n" +
        "  MAX(CSMI_Item_Name),\n" +
        "  --采购系统：物料名称\n" +
        "  MAX(CSMI_Marque),\n" +
        "  --采购系统：牌号\n" +
        "  MAX(CSMI_Specs),\n" +
        "  --采购系统：规格\n" +
        "  MAX(CSMI_Tech_Condition),\n" +
        "  --采购系统：技术条件\n" +
        "  MAX(CSMI_Gt_Code),\n" +
        "  --采购系统：附件技术条件\n" +
        "  MAX(CSMI_Mds_Item_Class_Code),\n" +
        "  --采购系统：物料类型代码\n" +
        "  MAX(CSMI_Mds_Item_Class_Name),\n" +
        "  --采购系统：物料类型名称\n" +
        "  MAX(CSMI_Mds_Item_Class_Desc),\n" +
        "  --采购系统：物料类型路径\n" +
        "  MAX(CSMI_Item_Type),\n" +
        "  --采购系统：物料大类\n" +
        "  ------------------------------------------------------------------------------------------------------------\n" +
        "  MAX(CSMI_RATION_UNIT_Desc),\n" +
        "  --采购系统：定额计量单位\n" +
        "  MAX(CSMI_WMS_RATION),\n" +
        "  --器材用量（库存单位）\n" +
        "  MAX(CSMI_WMS_Unit_Desc),\n" +
        "  --采购系统：库存计量单位\n" +
        "  MAX(\n" +
        "    coalesce(\n" +
        "      MATERIAL_Price,\n" +
        "      GN_planned_price,\n" +
        "      JJ_planned_price,\n" +
        "      JMY21_planned_price,\n" +
        "      JM_planned_price,\n" +
        "      ZB_planned_price\n" +
        "    )\n" +
        "  ),\n" +
        "  --计划价格\n" +
        "  MAX(\n" +
        "    CSRY_SINGLE_RATION / CSMI_RATION_TO_WMS * coalesce(\n" +
        "      MATERIAL_Price,\n" +
        "      GN_planned_price,\n" +
        "      JJ_planned_price,\n" +
        "      JMY21_planned_price,\n" +
        "      JM_planned_price,\n" +
        "      ZB_planned_price\n" +
        "    )\n" +
        "  ) AS JE,\n" +
        "  --器材金额\n" +
        "  MAX(DI_LOT) AS LOT,\n" +
        "  --执行计划架次\n" +
        "  MAX(MS_WORKLINE) AS MS_WORKLINE,\n" +
        "  --执行计划分工路线\n" +
        "  MAX(CSRY_EFFECTITVE_LOT) AS CSRY_EFFECTITVE_LOT,\n" +
        "  --材料定额：起架次\n" +
        "  MAX(CSRY_INEFFECTITVE_LOT) AS CSRY_INEFFECTITVE_LOT,\n" +
        "  --材料定额：止架次\n" +
        "  CSRY_DEPT_CODE AS CSRY_DEPT_CODE,\n" +
        "  --材料定额：部门代码原\n" +
        "  MAX(CSRY_N_DEPT) AS CSRY_N_DEPT,\n" +
        "  --材料定额：部门代码老\n" +
        "  MAX(CSRY_O_DEPT) AS CSRY_O_DEPT --材料定额：部门代码新\n" +
        "FROM\n" +
        "  MATERIAL_PLAN_relate MPL\n" +
        "where\n" +
        "  MPL.CSRY_MATERIAL_VALID = 1\n" +
        "GROUP BY\n" +
        "  date_source,\n" +
        "  MERP_EWO_ID,\n" +
        "  CSRY_DEPT_CODE,\n" +
        "  CSRY_MATERIAL_CODE;\n" +
        "--数据条数：\n" +
        "SELECT\n" +
        "  *\n" +
        "FROM\n" +
        "  xactest.COST_MATERIAL_PLAN_INFO;";
           // String s = formatSql(sql);
            List<DataLineageDto> result = parseViewDefinitionDruid(sql);
            System.out.println(result);
        } catch (IOException e) {
            // 处理读取文件时可能出现的异常
            e.printStackTrace();
        }


    }
}


